{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2fa770df",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "# import xlwt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8efd082e",
   "metadata": {},
   "outputs": [],
   "source": [
    "dir = \"E:\\\\HQData\\\\future\\\\\"\n",
    "file_dec = \"fut_dce.csv\"\n",
    "file_czce =\"fut_czce.csv\"\n",
    "file_ine = \"fut_ine.csv\"\n",
    "file_shf = \"fut_shf.csv\"\n",
    "\n",
    "file_main_dec = \"main_dce.csv\"\n",
    "file_main_czce = \"main_czce.csv\"\n",
    "file_main_ine = \"main_ine.csv\"\n",
    "file_main_shf = \"main_shf.csv\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4baf8f02",
   "metadata": {},
   "outputs": [],
   "source": [
    "basefiles=list()\n",
    "basefiles.append(file_dec)\n",
    "basefiles.append(file_czce)\n",
    "basefiles.append(file_ine)\n",
    "basefiles.append(file_shf)\n",
    "\n",
    "mainfiles = list()\n",
    "mainfiles.append(file_main_dec)\n",
    "mainfiles.append(file_main_czce)\n",
    "mainfiles.append(file_main_ine)\n",
    "mainfiles.append(file_main_shf)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a53d605c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>exchange</th>\n",
       "      <th>name</th>\n",
       "      <th>fut_code</th>\n",
       "      <th>multiplier</th>\n",
       "      <th>trade_unit</th>\n",
       "      <th>per_unit</th>\n",
       "      <th>quote_unit</th>\n",
       "      <th>quote_unit_desc</th>\n",
       "      <th>d_mode_desc</th>\n",
       "      <th>list_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>d_month</th>\n",
       "      <th>last_ddate</th>\n",
       "      <th>trade_time_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>JD1907.DCE</td>\n",
       "      <td>JD1907</td>\n",
       "      <td>DCE</td>\n",
       "      <td>鸡蛋1907</td>\n",
       "      <td>JD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>5.0</td>\n",
       "      <td>人民币元/500千克</td>\n",
       "      <td>1人民币元/500千克</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20180727</td>\n",
       "      <td>20190726</td>\n",
       "      <td>201907</td>\n",
       "      <td>20190731</td>\n",
       "      <td>上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>CS1505.DCE</td>\n",
       "      <td>CS1505</td>\n",
       "      <td>DCE</td>\n",
       "      <td>玉米淀粉1505</td>\n",
       "      <td>CS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>10.0</td>\n",
       "      <td>人民币元/吨</td>\n",
       "      <td>1人民币元/吨</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20141219</td>\n",
       "      <td>20150515</td>\n",
       "      <td>201505</td>\n",
       "      <td>20150520</td>\n",
       "      <td>上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>JM2304.DCE</td>\n",
       "      <td>JM2304</td>\n",
       "      <td>DCE</td>\n",
       "      <td>焦煤2304</td>\n",
       "      <td>JM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>60.0</td>\n",
       "      <td>人民币元/吨</td>\n",
       "      <td>0.5人民币元/吨</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20220419</td>\n",
       "      <td>20230417</td>\n",
       "      <td>202304</td>\n",
       "      <td>20230420</td>\n",
       "      <td>上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>C1003.DCE</td>\n",
       "      <td>C1003</td>\n",
       "      <td>DCE</td>\n",
       "      <td>玉米1003</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>10.0</td>\n",
       "      <td>人民币元/吨</td>\n",
       "      <td>1人民币元/吨</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20090316</td>\n",
       "      <td>20100312</td>\n",
       "      <td>201003</td>\n",
       "      <td>20100316</td>\n",
       "      <td>上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>M1708.DCE</td>\n",
       "      <td>M1708</td>\n",
       "      <td>DCE</td>\n",
       "      <td>豆粕1708</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>10.0</td>\n",
       "      <td>人民币元/吨</td>\n",
       "      <td>1人民币元/吨</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20160815</td>\n",
       "      <td>20170814</td>\n",
       "      <td>201708</td>\n",
       "      <td>20170817</td>\n",
       "      <td>上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0     ts_code  symbol exchange      name fut_code  multiplier  \\\n",
       "0           0  JD1907.DCE  JD1907      DCE    鸡蛋1907       JD         NaN   \n",
       "1           1  CS1505.DCE  CS1505      DCE  玉米淀粉1505       CS         NaN   \n",
       "2           2  JM2304.DCE  JM2304      DCE    焦煤2304       JM         NaN   \n",
       "3           3   C1003.DCE   C1003      DCE    玉米1003        C         NaN   \n",
       "4           4   M1708.DCE   M1708      DCE    豆粕1708        M         NaN   \n",
       "\n",
       "  trade_unit  per_unit  quote_unit quote_unit_desc d_mode_desc  list_date  \\\n",
       "0          吨       5.0  人民币元/500千克     1人民币元/500千克        实物交割   20180727   \n",
       "1          吨      10.0      人民币元/吨         1人民币元/吨        实物交割   20141219   \n",
       "2          吨      60.0      人民币元/吨       0.5人民币元/吨        实物交割   20220419   \n",
       "3          吨      10.0      人民币元/吨         1人民币元/吨        实物交割   20090316   \n",
       "4          吨      10.0      人民币元/吨         1人民币元/吨        实物交割   20160815   \n",
       "\n",
       "   delist_date  d_month  last_ddate  \\\n",
       "0     20190726   201907    20190731   \n",
       "1     20150515   201505    20150520   \n",
       "2     20230417   202304    20230420   \n",
       "3     20100312   201003    20100316   \n",
       "4     20170814   201708    20170817   \n",
       "\n",
       "                                trade_time_desc  \n",
       "0       上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间  \n",
       "1  上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)  \n",
       "2  上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)  \n",
       "3  上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)  \n",
       "4  上午9:00-11:30,下午13:30-15:00,下午21:00-23:00(夜盘)  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dec = pd.read_csv(dir + file_dec)\n",
    "\n",
    "df_dec.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0659fd8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>exchange</th>\n",
       "      <th>name</th>\n",
       "      <th>fut_code</th>\n",
       "      <th>multiplier</th>\n",
       "      <th>trade_unit</th>\n",
       "      <th>per_unit</th>\n",
       "      <th>quote_unit</th>\n",
       "      <th>quote_unit_desc</th>\n",
       "      <th>d_mode_desc</th>\n",
       "      <th>list_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>d_month</th>\n",
       "      <th>last_ddate</th>\n",
       "      <th>trade_time_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2356</th>\n",
       "      <td>2356</td>\n",
       "      <td>JD2604.DCE</td>\n",
       "      <td>JD2604</td>\n",
       "      <td>DCE</td>\n",
       "      <td>鸡蛋2604</td>\n",
       "      <td>JD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>5.0</td>\n",
       "      <td>人民币元/500千克</td>\n",
       "      <td>1人民币元/500千克</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20250428</td>\n",
       "      <td>20260427</td>\n",
       "      <td>202604</td>\n",
       "      <td>20260430</td>\n",
       "      <td>上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2473</th>\n",
       "      <td>2473</td>\n",
       "      <td>JD2603.DCE</td>\n",
       "      <td>JD2603</td>\n",
       "      <td>DCE</td>\n",
       "      <td>鸡蛋2603</td>\n",
       "      <td>JD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>5.0</td>\n",
       "      <td>人民币元/500千克</td>\n",
       "      <td>1人民币元/500千克</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20250327</td>\n",
       "      <td>20260326</td>\n",
       "      <td>202603</td>\n",
       "      <td>20260331</td>\n",
       "      <td>上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1145</th>\n",
       "      <td>1145</td>\n",
       "      <td>JD2602.DCE</td>\n",
       "      <td>JD2602</td>\n",
       "      <td>DCE</td>\n",
       "      <td>鸡蛋2602</td>\n",
       "      <td>JD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>5.0</td>\n",
       "      <td>人民币元/500千克</td>\n",
       "      <td>1人民币元/500千克</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20250226</td>\n",
       "      <td>20260224</td>\n",
       "      <td>202602</td>\n",
       "      <td>20260227</td>\n",
       "      <td>上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1539</th>\n",
       "      <td>1539</td>\n",
       "      <td>JD2601.DCE</td>\n",
       "      <td>JD2601</td>\n",
       "      <td>DCE</td>\n",
       "      <td>鸡蛋2601</td>\n",
       "      <td>JD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>5.0</td>\n",
       "      <td>人民币元/500千克</td>\n",
       "      <td>1人民币元/500千克</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20250123</td>\n",
       "      <td>20260122</td>\n",
       "      <td>202601</td>\n",
       "      <td>20260127</td>\n",
       "      <td>上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2236</th>\n",
       "      <td>2236</td>\n",
       "      <td>JD2512.DCE</td>\n",
       "      <td>JD2512</td>\n",
       "      <td>DCE</td>\n",
       "      <td>鸡蛋2512</td>\n",
       "      <td>JD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>吨</td>\n",
       "      <td>5.0</td>\n",
       "      <td>人民币元/500千克</td>\n",
       "      <td>1人民币元/500千克</td>\n",
       "      <td>实物交割</td>\n",
       "      <td>20241227</td>\n",
       "      <td>20251226</td>\n",
       "      <td>202512</td>\n",
       "      <td>20251231</td>\n",
       "      <td>上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0     ts_code  symbol exchange    name fut_code  multiplier  \\\n",
       "2356        2356  JD2604.DCE  JD2604      DCE  鸡蛋2604       JD         NaN   \n",
       "2473        2473  JD2603.DCE  JD2603      DCE  鸡蛋2603       JD         NaN   \n",
       "1145        1145  JD2602.DCE  JD2602      DCE  鸡蛋2602       JD         NaN   \n",
       "1539        1539  JD2601.DCE  JD2601      DCE  鸡蛋2601       JD         NaN   \n",
       "2236        2236  JD2512.DCE  JD2512      DCE  鸡蛋2512       JD         NaN   \n",
       "\n",
       "     trade_unit  per_unit  quote_unit quote_unit_desc d_mode_desc  list_date  \\\n",
       "2356          吨       5.0  人民币元/500千克     1人民币元/500千克        实物交割   20250428   \n",
       "2473          吨       5.0  人民币元/500千克     1人民币元/500千克        实物交割   20250327   \n",
       "1145          吨       5.0  人民币元/500千克     1人民币元/500千克        实物交割   20250226   \n",
       "1539          吨       5.0  人民币元/500千克     1人民币元/500千克        实物交割   20250123   \n",
       "2236          吨       5.0  人民币元/500千克     1人民币元/500千克        实物交割   20241227   \n",
       "\n",
       "      delist_date  d_month  last_ddate  \\\n",
       "2356     20260427   202604    20260430   \n",
       "2473     20260326   202603    20260331   \n",
       "1145     20260224   202602    20260227   \n",
       "1539     20260122   202601    20260127   \n",
       "2236     20251226   202512    20251231   \n",
       "\n",
       "                              trade_time_desc  \n",
       "2356  上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间  \n",
       "2473  上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间  \n",
       "1145  上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间  \n",
       "1539  上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间  \n",
       "2236  上午9:00-11:30，下午13:30-15:00，以及交易所规定的其他时间  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_dec.count\n",
    "df = df_dec[df_dec[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9cc5c447",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fut_dce.csv\n",
      "fut_czce.csv\n",
      "fut_ine.csv\n",
      "fut_shf.csv\n",
      "main_dce.csv\n",
      "main_czce.csv\n",
      "main_ine.csv\n",
      "main_shf.csv\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:7: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_base = df_all_base.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:7: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_base = df_all_base.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:7: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_base = df_all_base.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:7: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_base = df_all_base.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:18: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:18: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:18: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_4448\\3434181895.py:18: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n"
     ]
    }
   ],
   "source": [
    "\n",
    "df_all_base = pd.DataFrame()\n",
    "\n",
    "for file in basefiles:\n",
    "    print(file)\n",
    "    df_temp = pd.read_csv(dir + file)\n",
    "\n",
    "    df_all_base = df_all_base.append(df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False))\n",
    "    #\n",
    "df_all_base.to_csv(f'{dir}20250513_alltrade.csv')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "82fd8353",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "main_dce.csv\n",
      "main_czce.csv\n",
      "main_ine.csv\n",
      "main_shf.csv\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_3444\\3219728552.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp.sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_3444\\3219728552.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp.sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_3444\\3219728552.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp.sort_values(by=['name', 'ts_code'], ascending=False))\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_3444\\3219728552.py:9: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  df_all_main = df_all_main.append(df_temp.sort_values(by=['name', 'ts_code'], ascending=False))\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>exchange</th>\n",
       "      <th>name</th>\n",
       "      <th>fut_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <th>PG.DCE</th>\n",
       "      <th>PG</th>\n",
       "      <th>DCE</th>\n",
       "      <th>LPG主力</th>\n",
       "      <th>PG</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>PGL.DCE</th>\n",
       "      <th>PGL</th>\n",
       "      <th>DCE</th>\n",
       "      <th>LPG连续</th>\n",
       "      <th>PG</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>EB.DCE</th>\n",
       "      <th>EB</th>\n",
       "      <th>DCE</th>\n",
       "      <th>苯乙烯主力</th>\n",
       "      <th>EB</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>EBL.DCE</th>\n",
       "      <th>EBL</th>\n",
       "      <th>DCE</th>\n",
       "      <th>苯乙烯连续</th>\n",
       "      <th>EB</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <th>JL.DCE</th>\n",
       "      <th>JL</th>\n",
       "      <th>DCE</th>\n",
       "      <th>焦炭连续</th>\n",
       "      <th>J</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    ts_code  symbol  exchange  \\\n",
       "0 PG.DCE  PG  DCE LPG主力 PG NaN NaN NaN NaN NaN NaN      NaN     NaN       NaN   \n",
       "1 PGL.DCE PGL DCE LPG连续 PG NaN NaN NaN NaN NaN NaN      NaN     NaN       NaN   \n",
       "2 EB.DCE  EB  DCE 苯乙烯主力 EB NaN NaN NaN NaN NaN NaN      NaN     NaN       NaN   \n",
       "3 EBL.DCE EBL DCE 苯乙烯连续 EB NaN NaN NaN NaN NaN NaN      NaN     NaN       NaN   \n",
       "4 JL.DCE  JL  DCE 焦炭连续  J  NaN NaN NaN NaN NaN NaN      NaN     NaN       NaN   \n",
       "\n",
       "                                                    name  fut_code  \n",
       "0 PG.DCE  PG  DCE LPG主力 PG NaN NaN NaN NaN NaN NaN   NaN       NaN  \n",
       "1 PGL.DCE PGL DCE LPG连续 PG NaN NaN NaN NaN NaN NaN   NaN       NaN  \n",
       "2 EB.DCE  EB  DCE 苯乙烯主力 EB NaN NaN NaN NaN NaN NaN   NaN       NaN  \n",
       "3 EBL.DCE EBL DCE 苯乙烯连续 EB NaN NaN NaN NaN NaN NaN   NaN       NaN  \n",
       "4 JL.DCE  JL  DCE 焦炭连续  J  NaN NaN NaN NaN NaN NaN   NaN       NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = [\"ts_code\",\"symbol\",\"exchange\",\"name\",\"fut_code\"]\n",
    "    \n",
    "df_all_main = pd.DataFrame()\n",
    "\n",
    "for file in mainfiles:\n",
    "    print(file)\n",
    "    df_temp = pd.read_csv(dir + file, names=columns, header=0, skiprows=0)\n",
    "\n",
    "    df_all_main = df_all_main.append(df_temp.sort_values(by=['name', 'ts_code'], ascending=False))\n",
    "    # df.to_csv(f'{dir}20250508_trade_{file}')\n",
    "    \n",
    "df_all_main.head()\n",
    "#df_all_main.to_csv(f'{dir}20250513_allmain.csv')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "92fe4af8",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_csv?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "fdc21c1f",
   "metadata": {},
   "outputs": [
    {
     "ename": "BadZipFile",
     "evalue": "File is not a zip file",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mBadZipFile\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[31], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mopenpyxl\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m load_workbook\n\u001b[1;32m----> 3\u001b[0m book \u001b[38;5;241m=\u001b[39m load_workbook(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mdir\u001b[39m\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124mtrade20250528.xlsx\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m      4\u001b[0m writer \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mExcelWriter(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mdir\u001b[39m\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124mtrade20250528.xlsx\u001b[39m\u001b[38;5;124m'\u001b[39m, engine\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mopenpyxl\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m      5\u001b[0m writer\u001b[38;5;241m.\u001b[39mbook \u001b[38;5;241m=\u001b[39m book\n",
      "File \u001b[1;32mD:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\reader\\excel.py:315\u001b[0m, in \u001b[0;36mload_workbook\u001b[1;34m(filename, read_only, keep_vba, data_only, keep_links)\u001b[0m\n\u001b[0;32m    288\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mload_workbook\u001b[39m(filename, read_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_vba\u001b[38;5;241m=\u001b[39mKEEP_VBA,\n\u001b[0;32m    289\u001b[0m                   data_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_links\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m):\n\u001b[0;32m    290\u001b[0m \u001b[38;5;250m    \u001b[39m\u001b[38;5;124;03m\"\"\"Open the given filename and return the workbook\u001b[39;00m\n\u001b[0;32m    291\u001b[0m \n\u001b[0;32m    292\u001b[0m \u001b[38;5;124;03m    :param filename: the path to open or a file-like object\u001b[39;00m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    313\u001b[0m \n\u001b[0;32m    314\u001b[0m \u001b[38;5;124;03m    \"\"\"\u001b[39;00m\n\u001b[1;32m--> 315\u001b[0m     reader \u001b[38;5;241m=\u001b[39m ExcelReader(filename, read_only, keep_vba,\n\u001b[0;32m    316\u001b[0m                         data_only, keep_links)\n\u001b[0;32m    317\u001b[0m     reader\u001b[38;5;241m.\u001b[39mread()\n\u001b[0;32m    318\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m reader\u001b[38;5;241m.\u001b[39mwb\n",
      "File \u001b[1;32mD:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\reader\\excel.py:124\u001b[0m, in \u001b[0;36mExcelReader.__init__\u001b[1;34m(self, fn, read_only, keep_vba, data_only, keep_links)\u001b[0m\n\u001b[0;32m    122\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m__init__\u001b[39m(\u001b[38;5;28mself\u001b[39m,  fn, read_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_vba\u001b[38;5;241m=\u001b[39mKEEP_VBA,\n\u001b[0;32m    123\u001b[0m               data_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m, keep_links\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m):\n\u001b[1;32m--> 124\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39marchive \u001b[38;5;241m=\u001b[39m _validate_archive(fn)\n\u001b[0;32m    125\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mvalid_files \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39marchive\u001b[38;5;241m.\u001b[39mnamelist()\n\u001b[0;32m    126\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mread_only \u001b[38;5;241m=\u001b[39m read_only\n",
      "File \u001b[1;32mD:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\reader\\excel.py:96\u001b[0m, in \u001b[0;36m_validate_archive\u001b[1;34m(filename)\u001b[0m\n\u001b[0;32m     89\u001b[0m             msg \u001b[38;5;241m=\u001b[39m (\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mopenpyxl does not support \u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m file format, \u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m     90\u001b[0m                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mplease check you can open \u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m     91\u001b[0m                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mit with Excel first. \u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m     92\u001b[0m                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mSupported formats are: \u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m'\u001b[39m) \u001b[38;5;241m%\u001b[39m (file_format,\n\u001b[0;32m     93\u001b[0m                                                    \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m,\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;241m.\u001b[39mjoin(SUPPORTED_FORMATS))\n\u001b[0;32m     94\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m InvalidFileException(msg)\n\u001b[1;32m---> 96\u001b[0m archive \u001b[38;5;241m=\u001b[39m ZipFile(filename, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m     97\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m archive\n",
      "File \u001b[1;32mD:\\ProgramData\\anaconda3\\Lib\\zipfile.py:1302\u001b[0m, in \u001b[0;36mZipFile.__init__\u001b[1;34m(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)\u001b[0m\n\u001b[0;32m   1300\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m   1301\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m mode \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mr\u001b[39m\u001b[38;5;124m'\u001b[39m:\n\u001b[1;32m-> 1302\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_RealGetContents()\n\u001b[0;32m   1303\u001b[0m     \u001b[38;5;28;01melif\u001b[39;00m mode \u001b[38;5;129;01min\u001b[39;00m (\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mw\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mx\u001b[39m\u001b[38;5;124m'\u001b[39m):\n\u001b[0;32m   1304\u001b[0m         \u001b[38;5;66;03m# set the modified flag so central directory gets written\u001b[39;00m\n\u001b[0;32m   1305\u001b[0m         \u001b[38;5;66;03m# even if no files are added to the archive\u001b[39;00m\n\u001b[0;32m   1306\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_didModify \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mTrue\u001b[39;00m\n",
      "File \u001b[1;32mD:\\ProgramData\\anaconda3\\Lib\\zipfile.py:1369\u001b[0m, in \u001b[0;36mZipFile._RealGetContents\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1367\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m BadZipFile(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mFile is not a zip file\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   1368\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m endrec:\n\u001b[1;32m-> 1369\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m BadZipFile(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mFile is not a zip file\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   1370\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdebug \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[0;32m   1371\u001b[0m     \u001b[38;5;28mprint\u001b[39m(endrec)\n",
      "\u001b[1;31mBadZipFile\u001b[0m: File is not a zip file"
     ]
    }
   ],
   "source": [
    "#from openpyxl import load_workbook\n",
    "\n",
    "book = load_workbook(f'{dir}trade20250528.xlsx')\n",
    "writer = pd.ExcelWriter(f'{dir}trade20250528.xlsx', engine='openpyxl')\n",
    "writer.book = book\n",
    "\n",
    "for file in files:\n",
    "    print(file)\n",
    "    df_temp = pd.read_csv(dir + file)\n",
    "\n",
    "    df = df_temp[df_temp[\"delist_date\"] > 20250501].sort_values(by=['name', 'ts_code'], ascending=False)\n",
    "    # df.to_csv(f'{dir}20250508_trade_{file}')\n",
    "    #df.to_excel(writer , sheet_name=file.replace('.csv','') )\n",
    "    \n",
    "#writer.save()\n",
    "#writer.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b5f5547c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_28776\\629712453.py:1: FutureWarning: save is not part of the public API, usage can give unexpected results and will be removed in a future version\n",
      "  writer.save()\n"
     ]
    }
   ],
   "source": [
    "\n",
    "writer.save()\n",
    "writer.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
